﻿using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core;

namespace EPPlus_ExcelDemo
{
    public static class Miscellaneous
    {
        public static void InsertControl(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input, ExcelVersion.Version2010);
            Worksheet ws = wb.Worksheets[0];

            ITextBoxShape textbox = ws.TextBoxes.AddTextBox(16, 1, 20, 100);
            textbox.Text = "Hello World";
            textbox.VAlignment = CommentVAlignType.Center;

            ICheckBox checkbox = ws.CheckBoxes.AddCheckBox(18, 1, 15, 100);
            checkbox.Text = "Check me";
            checkbox.CheckState = CheckState.Checked;

            IRadioButton radioButton = ws.RadioButtons.Add(20, 1, 15, 100);
            radioButton.Text = "Check me";
            radioButton.CheckState = CheckState.Checked;

            wb.SaveToFile(output, ExcelVersion.Version2010);
        }

        public static void InsertObject(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input, ExcelVersion.Version2010);
            Worksheet ws = wb.Worksheets[0];

            ws.OleObjects.Add("../../Documents/Document.pdf", Image.FromFile("../../Documents/PDF.png"), OleLinkType.Embed);

            wb.SaveToFile(output, ExcelVersion.Version2010);
        }

        public static void SaveChartToPicture(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input, ExcelVersion.Version2010);
            Image[] imgs = wb.SaveChartAsImage(wb.Worksheets[0]);
            for (int i = 0; i < imgs.Length; i++)
            {
                imgs[i].Save(string.Format("{0}-{1}.png", output, i));
            }
        }

        public static void InsertSparklines(string input, string output)
        {
            Workbook wb = new Workbook();
            wb.LoadFromFile(input, ExcelVersion.Version2010);
            Worksheet ws = wb.Worksheets[0];

            SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Line);
            SparklineCollection sparklines = sparklineGroup.Add();
            //sparklines.Add(ws["D3:G3"], ws["H3"]);
            //sparklines.Add(ws["D4:G4"], ws["H4"]);
            //sparklines.Add(ws["D5:G5"], ws["H5"]);
            //sparklines.Add(ws["D6:G6"], ws["H6"]);
            sparklines.Add(ws["B2:G14"], ws["H2:H14"]);

            wb.SaveToFile(output, ExcelVersion.Version2010);
        }

    }
}
